﻿using Dapper;
using Domain.Entity;
using Domain.Entity.tr;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class Allot_dRepository : IAllot_dRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";

        //查所有
        public async Task<List<Allot_d>> GetAll()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"SELECT [ald_id],[al_id],[proc_id],[proc_name],[ald_num],[ald_price],[ald_total],[instock_status],[outstock_status],[ald_outstockOK_num],[ald_outstockNO_num],[ald_instockOK_num],[ald_instockNO_num],[remark] FROM [dbo].[tr_allot_d]";
                return sqlConnection.Query<Allot_d>(sql).ToList();
            }
        }

        // 批量查询所有指定调拨ID的数据
        public async Task<List<Allot_d>> GetAid(int[] ids)
        {
            // 如果传入空数组，直接返回空结果
            if (ids == null || ids.Length == 0)
                return new List<Allot_d>();

            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 使用参数化IN查询防止SQL注入
                string sql = $@"
            SELECT [ald_id],[al_id],[proc_id],[proc_name],[ald_num],[ald_price],[ald_total],
                   [instock_status],[outstock_status],[ald_outstockOK_num],[ald_outstockNO_num],
                   [ald_instockOK_num],[ald_instockNO_num],[remark] 
            FROM [dbo].[tr_allot_d] 
            WHERE al_id IN @ids";

                // 直接传递数组参数，Dapper会自动展开为参数化查询
                var parameters = new { ids };

                // 执行查询并返回结果
                return (await sqlConnection.QueryAsync<Allot_d>(sql, parameters)).ToList();
            }
        }

        public async Task<List<Allot_d>> GetAllot_d(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"SELECT [ald_id],[al_id],[proc_id],[proc_name],[ald_num],[ald_price],[ald_total],[instock_status],[outstock_status],[ald_outstockOK_num],[ald_outstockNO_num],[ald_instockOK_num],[ald_instockNO_num],[remark] FROM [dbo].[tr_allot_d] WHERE  al_id = {id}";
                return sqlConnection.Query<Allot_d>(sql).ToList();
            }
        }

        //统计调拨
        public async Task<FenYe<AllotTJResult>> GetAllotTJResult(FenYe<AllotTJResult> fenYe, string? proc_code, string? al_code, string? al_type, string? header_create_by, int? type_id, string? proc_primary_unit, string? al_user, int? out_wh_id, int? in_wh_id, int[] instock_status, int[] outstock_status, string? startTime, string? endTime, string? createstartTime, string? createendTime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 初始化基础WHERE条件
                var whereBuilder = new StringBuilder("header_is_delete = 0 AND product_is_delete = 0");

                // 添加可选条件（直接拼接值）
                if (!string.IsNullOrEmpty(proc_code))
                {
                    whereBuilder.Append($" AND (product_name LIKE '%{proc_code}%' OR proc_code LIKE '%{proc_code}%')");
                }

                if (!string.IsNullOrEmpty(al_code))
                {
                    whereBuilder.Append($" AND al_code LIKE '%{al_code}%'");
                }

                if (!string.IsNullOrEmpty(al_type))
                {
                    whereBuilder.Append($" AND al_type = '{al_type}'");
                }

                if (!string.IsNullOrEmpty(header_create_by))
                {
                    whereBuilder.Append($" AND header_create_by LIKE '%{header_create_by}%'");
                }

                if (!string.IsNullOrEmpty(proc_primary_unit))
                {
                    whereBuilder.Append($" AND proc_primary_unit = '{proc_primary_unit}'");
                }

                if (!string.IsNullOrEmpty(al_user))
                {
                    whereBuilder.Append($" AND al_user LIKE '%{al_user}%'");
                }

                if (type_id.HasValue)
                {
                    whereBuilder.Append($" AND type_id = {type_id.Value}");
                }

                if (out_wh_id.HasValue)
                {
                    whereBuilder.Append($" AND out_wh_id = {out_wh_id.Value}");
                }

                if (in_wh_id.HasValue)
                {
                    whereBuilder.Append($" AND in_wh_id = {in_wh_id.Value}");
                }

                if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    whereBuilder.Append($" AND al_date BETWEEN '{startTime}' AND '{endTime}'");
                }

                if (!string.IsNullOrEmpty(createstartTime) && !string.IsNullOrEmpty(createendTime))
                {
                    whereBuilder.Append($" AND header_create_time BETWEEN '{createstartTime}' AND '{createendTime}'");
                }

                // 处理 instock_status 和 outstock_status
                if ((instock_status != null && instock_status.Length > 0) ||
                    (outstock_status != null && outstock_status.Length > 0))
                {
                    string orCondition = "";
                    if (instock_status != null && instock_status.Length > 0)
                        orCondition += $"detail_instock_status IN ({string.Join(",", instock_status)})";

                    if (outstock_status != null && outstock_status.Length > 0)
                    {
                        if (orCondition.Length > 0)
                            orCondition += " OR ";
                        orCondition += $"detail_outstock_status IN ({string.Join(",", outstock_status)})";
                    }

                    whereBuilder.Append($" AND ({orCondition})");
                }

                // 初始化分页参数（仅包含存储过程需要的参数）
                var parameters = new DynamicParameters();
                parameters.Add("@pageSize", 10);
                parameters.Add("@keyName", "al_id");
                parameters.Add("@tabelName", "vw_allotment_details");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@where", whereBuilder.ToString());
                parameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);

                // 执行存储过程
                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                var result = await sqlConnection.QueryAsync<AllotTJResult>(sql, parameters);

                fenYe.List = result.ToList();
                fenYe.Rows = parameters.Get<int>("@rows");

                return fenYe;
            }
        }

        public async Task<int> UpdateStatus(string nameok, int oknum, string nameno, int nonum, string name, int alid, int procid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[tr_allot_d] set {nameok}={oknum},{nameno}={nonum},{name}=2 where al_id={alid} and proc_id={procid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }
    }
}